
clear all

global afrobar="C:\Users\xisaka\ShareFile\Personal Folders\Afrobarometer"
global aiddata="C:\Users\xisaka\ShareFile\Personal Folders\AidData"
global china_ethnic= "C:\Users\xisaka\Dropbox\Ias\MINA PAPERS\chinese aid and ethnic sentiments\stata"
global temp="C:\Users\xisaka\ShareFile\Personal Folders\temp data"

set more off
set maxvar 32767









/***************NIGERIA AID DATA *****************/
/*************************************************/


set more off

import delimited 	"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\data\projects.csv", clear 
save 				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\data\projects.dta", replace


import delimited 	"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\data\locations.csv", clear 
save 				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\data\locations.dta", replace


*--> merge together 

use "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\data\projects.dta", clear
sort project_id
save "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\data\projects.dta", replace

use "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\data\locations.dta", clear
sort project_id
save "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\data\locations.dta", replace


use "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\data\projects.dta", clear
sort project_id
merge project_id using "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1\data\locations.dta"
tab _merge 
drop _merge
sort project_id

*only keep the geocoded projects
keep if is_geocoded==1			
  
*keep observations with more exact geocodes
keep if precision_code<3			
 
*Drop the ones that do not have start date----> no obs deleted, all had start date
drop if start_actual_isodate==""	

*fix start and end dates so they only give the years
 gen start_year	=substr(start_actual_isodate, 1,4)			//starts at the first character, saves 4 characters...
gen end_year	=substr(end_actual_isodate, 1,4)
 
destring start_year, replace
destring end_year, replace

	  
	
****restrict to relevant donors			   
			 
tab donors		

*restrict to bilateral donors (for comparison with china)
drop if donors=="United Nations Office for Project Services (UNOPS)"			//1 removed

keep project_id donors recipients project_location_id latitude longitude start_year end_year

destring project_id, ignore ("NGA/") replace

save "$aiddata\cleaned nigeria for appending.dta", replace







/*********************CLEANING THE UGANDA AID DATA *****************************/
/*******************************************************************************/


set more off



*först konvertera från csv-fil till stata:

import delimited 	"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\projects.csv", clear 
save 				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\projects.dta", replace

import delimited 	"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\locations.csv", clear 
save 				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\locations.dta", replace

import delimited 	"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\level_1a.csv", clear 
save 				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\level_1a.dta", replace

import delimited 	"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\transactions.csv", clear 
save 				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\transactions.dta", replace


*--> merge together (maybe I don't need the transactions?)
use "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\projects.dta", clear
sort project_id
save "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\projects.dta", replace

use "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\locations.dta", clear
sort project_id
save "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\locations.dta", replace

use "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\level_1a.dta", clear
sort project_id
save "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\level_1a.dta", replace


use "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\projects.dta", clear
merge project_id using "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\locations.dta"
tab _merge 	
drop _merge
sort project_id

merge project_id using "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1\data\level_1a.dta"
tab _merge	
drop _merge
sort project_id


*only keep the geocoded projects
keep if is_geocoded==1				//(1,144 observations deleted)
 
	
*keep observations with more exact geocodes
tab precision_code
keep if precision_code<3			// (1,588 observations deleted)
 

*Drop the ones that do not have start date----> no obs deleted, all had start date
drop if start_actual_isodate==""	//191 obs deleted


*fix start and end dates so they only give the years
gen start_year	=substr(start_actual_isodate, 1,4)			
gen end_year	=substr(end_actual_isodate, 1,4)
destring start_year, replace
destring end_year, replace


*restrict to relevant years, the chinese data covers 2000-2012, 		   
			   
drop if start_year<2000 | start_year>2012		   
		   	
****restrict to relevant donors			   
			   
tab donors		

*restrict to bilateral donors (excluding china)... maybe think about focusing only on US or japan since they have so many projects
			  		  
keep if donors=="Austria" |	donors=="Belgium|France" |	donors=="Germany" |	donors=="Ireland"  | donors=="Japan" |	donors=="Japan|International Development Association"  | donors=="Netherlands"  ///
| donors=="Norway" | donors=="Spain" | donors=="United States of America"


keep project_id donors recipients project_location_id latitude longitude start_year end_year


save "$aiddata\cleaned uganda for appending.dta", replace





*append the Nigeria data to the Uganda data:

append using "$aiddata\cleaned nigeria for appending.dta"





save "$aiddata\Nigera and Uganda clean.dta", replace





/***************CLEANING THE SENEGAL AID DATA FOR COMPARISON*****************/
/*******************************************************************************/


set more off



import delimited 	"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\projects.csv", clear
save				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\projects.dta", replace

import delimited 	"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\locations.csv", clear
save				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\locations.dta", replace

import delimited 	"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\level_1a.csv", clear
save				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\level_1a.dta", replace

import delimited 	"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\transactions.csv", clear
save				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\transactions.dta", replace



*--> merge together


use 				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\projects.dta", clear
sort project_id
save				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\projects.dta", replace

use 				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\locations.dta", clear
sort project_id
save 				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\locations.dta", replace

use 				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\level_1a.dta", clear
sort project_id
save 				"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\level_1a.dta", replace



use 					"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\projects.dta", clear
merge project_id using	"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\locations.dta"
tab _merge				//51% i master only, dvs ej geokodad
drop _merge
sort project_id

merge project_id using "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\SenegalAIMS_GeocodedResearchRelease_Level1_v1.5.1\data\level_1a.dta"
tab _merge	
drop _merge
sort project_id

*only keep the geocoded projects
keep if is_geocoded==1				//(1,176 observations deleted)
 

*keep observations with more exact geocodes
tab precision_code
keep if precision_code<3			// (772 observations deleted)

 
*Drop the ones that do not have start date
drop if start_actual_isodate==""	//180 obs deleted


*fix start and end dates so they only give the years
gen start_year	=substr(start_actual_isodate, 1,4)			
gen end_year	=substr(end_actual_isodate, 1,4)
destring start_year, replace
destring end_year, replace

*restrict to relevant years, the chinese data covers 2000-2012, restrict to these years. 			   
			   
drop if start_year<2000 | start_year>2012		   //no such obs
		   			      
****restrict to relevant donors			   
		  
tab donors		

tab donors_iso3

*restrict to bilateral donors (excluding china, and senegals own commercial bank)... 
keep if donors_iso3=="BEL" 	| donors_iso3=="DEU"		| donors_iso3=="FRA"	| donors_iso3=="JPN"	| donors_iso3=="SEN|BEL"	| donors_iso3=="SEN|DAC|ITA"	| donors_iso3=="SEN|DAC|USA|JPN"	///
							| donors_iso3=="SEN|FRA"	| donors_iso3=="SEN|FRA|DEU|DAC"	| donors_iso3=="SEN|KWT|DAC"	| donors_iso3=="SEN|SAU"	| donors_iso3=="USA"

keep project_id donors recipients project_location_id latitude longitude start_year end_year


save "$aiddata\cleaned senegal for appending.dta", replace










/***************CLEANING THE MALAWI AID DATA **********************************/
/*******************************************************************************/



*note: different names etc compared to in the other countries

set more off

import excel 	"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\Malawi_release_17april2012.xlsx\Malawi data.xlsx", sheet("Sheet1") firstrow clear
save 			"\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\Malawi_release_17april2012.xlsx\Malawi data.dta", replace

use "\\home.gu.gu.se\home-XI$\xisaka\Documents\DATA\Malawi_release_17april2012.xlsx\Malawi data.dta", clear


*only keep the geocoded projects
drop if Latitude==.			
 

*keep observations with more exact geocodes
 
tab Precision
keep if Precision<3			
 
 
 
*Drop the ones that do not have start date
*note: here called DateofAgreementSigned which according to codebook is 'Year the project was signed into implementation. In YYYY format'

drop if DateofAgreementSigned==.	//96 obs deleted

 
 
*fix start and end dates so they only give the years
 gen start_year		= DateofAgreementSigned 						//was already in the right form
gen end_year		= DateofPlannedCompletion						//Year the project is expected to be completed. In YYYY format
replace end_year 	= . if Status=="Planned" | Status=="Ongoing"	//if planned end year had not materialised at the time of the survey in 2012
replace start_year	= . if Status=="Planned" 						//implying that the planned implementation date did not take place, and project had yet to start at the time of the survey in 2012
drop if start_year 	==.												//can't have projects without proper start dates in sample


*gen start_year= substr(start_actual_isodate, 1,4)			
*gen end_year	=substr(end_actual_isodate, 1,4)
 
	   
drop if start_year<2000 | start_year>2012		   //no such obs
		   			   
codebook AMPProjectID 		//the project_id variable here, 103 unique values and 769 obs, meaning that we have several obs per project, need to create location id.
	
rename 	AMPProjectID project_id

tostring project_id, gen(projid) force format(%15.0g)		//without format stata rounds off numeric value before making string
tostring GeoNameID, gen(locid) force 
gen project_location_id = (projid) + "00000" + (locid) 


codebook project_id					//103 projects
codebook project_location_id		//748 project locations
 			   
			   
****restrict to relevant donors			   
			   

tab Donor

*restrict to bilateral donors (excluding china)
keep if Donor=="Australian Agency for International Development (AusAid)" 	| 	Donor=="Flemish International Cooperation Agency (FICA)"  	| 	Donor=="German Agency for International Cooperation (GIZ)" 	///
| 	Donor=="Icelandic International Development Agency (ICEIDA)" 	| 	Donor=="Irish Aid" 	| 	Donor=="Japan International Cooperation Agency (JICA)"	| 	Donor=="KFW Bankengruppe"	| 	Donor=="Kuwait Fund"	///
| 	Donor=="Norwegian Agency for Development Cooperation (NORAD)"	| 	Donor=="UK Department for International Development (DfID)"	///
| 	Donor=="US Agency for International Development"	| 	Donor=="US Center for Disease Control and Prevention (US CDC)"


 	
rename 	Donor 		donors
rename 	Recipient 	recipients
rename 	Latitude 	latitude
rename 	Longitude 	longitude

keep project_id donors recipients project_location_id latitude longitude start_year end_year


duplicates list project_id project_l
duplicates tag project_location_id, g(tag)
sort project_location_id
*br if tag==1								
duplicates drop
bysort project_location_id: gen id=_n 
replace project_location_id = project_location_id + "b" if id==2		//two obs have slightly diff lat and long but all else equal. now distinguish between the two
*replace project_location_id = project_location_id + "c" if id==3
drop id


save "$aiddata\cleaned malawi for appending.dta", replace




/**************append the Senegal and Malawi data to the Uganda and Nigeria data:***************/

use "$aiddata\Nigera and Uganda clean.dta", clear

append using  	"$aiddata\cleaned senegal for appending.dta"

append using 	"$aiddata\cleaned malawi for appending.dta"





tab donors
/*
                                 donors |      Freq.     Percent        Cum.
----------------------------------------+-----------------------------------
Australian Agency for International D.. |          7        0.81        0.81
                                Austria |          6        0.70        1.51
                                Belgium |          1        0.12        1.62
                         Belgium|France |          3        0.35        1.97
Belgium|International Commercial Bank.. |          1        0.12        2.09
                                 Canada |         11        1.28        3.36
Flemish International Cooperation Age.. |          4        0.46        3.83
                                 France |         25        2.90        6.73
German Agency for International Coope.. |         54        6.26       12.99
                         German Embassy |          2        0.23       13.23
                                Germany |          1        0.12       13.34
Icelandic International Development A.. |          6        0.70       14.04
International Commercial Bank Senegal.. |          1        0.12       14.15
International Commercial Bank Senegal.. |          3        0.35       14.50
                                Ireland |          8        0.93       15.43
                              Irish Aid |         11        1.28       16.71
Italy|International Commercial Bank S.. |          1        0.12       16.82
JICA - Japan International Cooperatio.. |          1        0.12       16.94
                                  Japan |        110       12.76       29.70
Japan International Cooperation Agenc.. |          1        0.12       29.81
Japan|International Development Assoc.. |          2        0.23       30.05
Japan|World Health Organization|USAID.. |         25        2.90       32.95
                       KFW Bankengruppe |          2        0.23       33.18
                                    KfW |          2        0.23       33.41
KfW|European Investment Bank|French D.. |          1        0.12       33.53
                            Kuwait Fund |          5        0.58       34.11
                            Netherlands |          1        0.12       34.22
                                 Norway |          2        0.23       34.45
Norwegian Agency for Development Coop.. |        117       13.57       48.03
Organization of the Petroleum Exporti.. |          3        0.35       48.38
                                  Spain |          2        0.23       48.61
                     Switzerland|Sweden |          6        0.70       49.30
UK Department for International Devel.. |         69        8.00       57.31
US Agency for International Development |          8        0.93       58.24
US Center for Disease Control and Pre.. |         12        1.39       59.63
                                  USAID |          2        0.23       59.86
                  United Kingdom (DFID) |          2        0.23       60.09
United States African Development Fou.. |         21        2.44       62.53
               United States of America |        323       37.47      100.00
----------------------------------------+-----------------------------------
                                  Total |        862      100.00
*/


*some that still need to be dropped, multilateral rather than bilateral

drop if donors=="KfW|European Investment Bank|French Development Agency|Investment Bank of Development and Cooperation|International Development Association|International Commercial Bank Senegal|African Development Bank/African Development Fund" ///
	| donors=="Japan|World Health Organization|USAID|International Commercial Bank Senegal|United Nations Population Fund"	///
	| donors=="Organization of the Petroleum Exporting Countries|Kuwait Fund|Arab Bank of Economic Development in Africa|Investment Bank of Development and Cooperation|International Commercial Bank Senegal|Islamic Development Bank"



				  
save "$aiddata\Bilateral to nig uga sen mal clean.dta", replace













****************************************GEOMATCHING THE AID DATA AND THE AFROBAROMETER DATA****************************************
***********************************************************************************************************************************




set more off
set matsize 800



**********open the collapsed Afrobar data

use "$afrobar/Afro_collapsed_precisioncode123_replication.dta", clear


keep if country=="Uganda" | country=="Nigeria" | country=="Malawi" | country=="Senegal"




** Add the aid projects		
append using   "$aiddata\Bilateral to nig uga sen mal clean.dta", keep(project_location_id  latitude longitude)		

** Sort so that nearstat will run, putting all projects first
*gsort project_location_id
* Not numeric so it dd not put the projects first.
gsort latitude









/*

*creating a variable combining latitude, longitude and surevy wave
egen cluster2=group(g_lat g_lon wave)


collapse (first) g_lat g_lon wave, by(cluster2)
*/






	

** First count how many projects are within 200 km to know how many loops to run nearstat
set more off
foreach dist in 10 25 50 75 100 200 {
	nearstat g_lat g_lon, ///
		near(latitude longitude) distvar(_1projectdist`dist') ///
		ncount(numberwithin`dist') dband(0 `dist') favor(speed)
}
drop _1projectdist10 _1projectdist25 _1projectdist50 _1projectdist75 _1projectdist100 _1projectdist200

** Run nearstat loop, with number of loops equal to max of numberwithin200
sum numberwithin200
forvalues i = 1/`r(max)' {
	nearstat g_lat g_lon if `i'<=numberwithin200, ///
		near(latitude longitude) distvar(_`i'projectdist) nid(project_location_id _`i'closestproject) ///
		kth(`i') favor(speed)
}

set more off


** Add the closest project to those that don't have projects within 200km.
nearstat g_lat g_lon if numberwithin200==0, ///
	near(latitude longitude) distvar(not200_projectdist) nid(project_location_id not200_closestproject) ///
	kth(1) favor(speed)
	
* Temporary save if something goes wrong
save "$temp/temp_aid.dta", replace





replace _1closestproject=not200_closestproject if numberwithin200==0
replace _1projectdist=not200_projectdist if numberwithin200==0

*Now drop all projects (they are now linked to clusters rowwise)
drop if !missing(latitude)
drop project_location_id latitude longitude
drop if missing(g_lat)

compress



** Merge with detailed data on projects				
set more off
sum numberwithin200
forvalues i = 1/`r(max)' {
	rename _`i'closestproject project_location_id
	merge m:1 project_location_id using "$aiddata\Bilateral to nig uga sen mal clean.dta", keepusing(project_id start_year end_year recipients)
	rename project_id _`i'project_id
	rename start_year _`i'startyear
	rename end_year _`i'endyear
	rename project_location_id _`i'closestproject 
	drop if _merge == 2
	drop _merge
}

compress



** Merge back with precollapse data. Now all respondents with the same coordinates will have exactly the same projects identified nearby. 
set more off
merge 1:m g_lat g_lon using "$afrobar/Afro_tot_precisioncode123_replication.dta"
drop if _merge==2
drop _merge

save "$temp/temp_aidC.dta", replace





use "$temp/temp_aidC.dta", clear

destring *startyear, replace		

destring *endyear, replace


set more off
foreach dist in 25 50 75 100 200 {
	gen byte active`dist' = .
	gen byte suspended`dist' = .
	gen byte inactive`dist' = .
	gen byte activecodehelp`dist' = .
	sum numberwithin`dist'
	forvalues i = 1/`r(max)' {
		* Create a helpvariable to be used for suspended
		replace activecodehelp`dist' = 1 if _`i'startyear<=int_year & _`i'projectdist <= `dist' & _`i'endyear>int_year
		* Resp active if any project is active within X km in or after interview year
		replace active`dist' = 1 if _`i'startyear<=int_year & _`i'projectdist <= `dist' 
		label variable active`dist' "Active if any project is active"
		* Resp suspended if any project is suspended within X km
		replace suspended`dist' = 1 if _`i'endyear<int_year & _`i'endyear<.  & _`i'projectdist <= `dist' 
		* But not if at least one project is active within X km
		replace suspended`dist' = 0 if activecodehelp`dist'==1
		label variable suspended`dist' "Suspended if all projects have ended"
		* Resp inactive if any project is inactive within X km
		replace inactive`dist' = 1 if _`i'startyear>int_year & _`i'projectdist <= `dist'
		label variable inactive`dist' "Inactive if a project will start, conditional on no projects before"
		* But not if at least one project is either active or suspended within X km
		* Area considered "contaminated"
		replace inactive`dist' = 0 if active`dist'==1 | suspended`dist'==1

	}
	* Resp not active/inactive/suspended if never replaced in the loop above
	replace active`dist'=0 if missing(active`dist')
	replace inactive`dist'=0 if missing(inactive`dist')
	replace suspended`dist'=0 if missing(suspended`dist')
	noisily di as text "Calculated distance " as result `dist'
}


compress
save "$temp/temp_aid2C.dta", replace		//large file




*generate country and year dummies (and region dummies if we want to use that)
tab int_year, gen(yd)
tab country, gen(cd) 
tab region, gen (rd)

* Generate distance variable
gen distance=_1projectdist





rename active25 		ongoing25
rename inactive25 		future25
rename active50 		ongoing50
rename inactive50 		future50
rename active75 		ongoing75
rename inactive75 		future75
rename active100 		ongoing100
rename inactive100 		future100
rename active200 		ongoing200
rename inactive200		future200	  
rename suspended25 		completed25
rename suspended50 		completed50
rename suspended75 		completed75
rename suspended100 	completed100
rename suspended200		completed200

gen preseth_ongoing50=presethnic*ongoing50
gen preseth_future50=presethnic*future50
gen preseth_ongoing25=presethnic*ongoing25
gen preseth_future25=presethnic*future25

gen anyproject50 =cond(numberwithin50>0 ,1,0) if numberwithin50!=.
gen anyproject75 =cond(numberwithin75>0 ,1,0) if numberwithin75!=.
gen anyproject25 =cond(numberwithin25>0 ,1,0) if numberwithin25!=.

*note: only uganda, senegal, malawi and nigeria in this dataset so no need for further restrictions in temrs of countries
gen 	ethnicestsample	=cond((ethnicidentity!=.	 & presethnic!=. & ongoing25!=. & future25!=.),1,0) 						//note: slight sample difference compared to previous version (an extra 100 obs or so) gives slight parameter difference in table c1








compress
save "$aiddata/bilateral to nig uga sen mal_afro precision code 1 2 3 data_replication.dta", replace

		
			
			
			
			
			
			
			



/*the end*/
